package com.css.fxfzxczh.modules.disasterSituationDataManage.lifeline.repository.Impl;

import com.css.fxfzxczh.modules.disasterSituationDataManage.lifeline.entity.Lifeline;
import com.css.fxfzxczh.modules.disasterSituationDataManage.lifeline.entity.LifelineType;
import com.css.fxfzxczh.modules.disasterSituationDataManage.lifeline.param.LifelineParam;
import com.css.fxfzxczh.modules.disasterSituationDataManage.lifeline.repository.LifelineRepository;
import com.css.fxfzxczh.modules.synthesizeInfoManage.houseStructureManagement.entity.HouseStructureManagement;
import com.css.fxfzxczh.util.PlatformObjectUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.lang.reflect.Field;
import java.sql.Timestamp;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author: zhangSongRui
 * @CreateTime: 2023/01/30
 * @Description: 生命线工程
 */
@Repository
public class LifelineRepositoryImpl implements LifelineRepository {
    @Resource
    JdbcTemplate jdbcTemplate;

    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    private static final String TABLE_NAME = "xczh_lifeline_review"; // 表名
    private static final String TABLE_ID = "xlr_id"; // 表id字段名

    @Override
    public Map<String, Object> getPage(LifelineParam queParams, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("select * from " + TABLE_NAME);
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0'");
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        //生命线破环情况
        if (!PlatformObjectUtils.isEmpty(queParams.getDamage())) {
            whereSql.append(" and damage = '" + queParams.getDamage() + "'");
        }
        //地震事件id
        if (!PlatformObjectUtils.isEmpty(queParams.getXsemId())) {
            whereSql.append(" and xsem_id = '" + queParams.getXsemId() + "'");
        }
        //生命线类型
        if (!PlatformObjectUtils.isEmpty(queParams.getLineType())) {
            whereSql.append(" and line_type = '" + queParams.getLineType() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getLineType2())) {
            whereSql.append(" and line_type2 = '" + queParams.getLineType2() + "'");
        }
        //生命线名称
        if (!PlatformObjectUtils.isEmpty(queParams.getLifelineName())) {
            whereSql.append(" and lifeline_name = '" + queParams.getLifelineName() + "'");
        }
        //调查员
        if (!PlatformObjectUtils.isEmpty(queParams.getInvestigator())) {
            whereSql.append(" and investigator  like '%" + queParams.getInvestigator() + "%'");
        }
        //生命线工程编号
        if (!PlatformObjectUtils.isEmpty(queParams.getLifelineCode())) {
            whereSql.append(" and lifeline_code like '%" + queParams.getLifelineCode() + "%'");
        }
        //推测烈度
        if (!PlatformObjectUtils.isEmpty(queParams.getIntensity())) {
            whereSql.append(" and intensity = '" + queParams.getIntensity() + "'");
        }
        //审核状态
        if (!PlatformObjectUtils.isEmpty(queParams.getStatus())) {
            whereSql.append(" and status = '" + queParams.getStatus() + "'");
        }
        //推测烈度
        if (!PlatformObjectUtils.isEmpty(queParams.getInvestigatorId())) {
            whereSql.append(" and investigator_id = '" + queParams.getInvestigatorId() + "'");
        }
        sql.append(whereSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(Lifeline.class)).size();
        List<Lifeline> list = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(Lifeline.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    @Override
    public List<Lifeline> queryByParam(LifelineParam queParams) {
        StringBuilder sql = new StringBuilder("select * from " + TABLE_NAME);
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0'");
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        //生命线破环情况
        if (!PlatformObjectUtils.isEmpty(queParams.getDamage())) {
            whereSql.append(" and damage = '" + queParams.getDamage() + "'");
        }
        //地震事件id
        if (!PlatformObjectUtils.isEmpty(queParams.getXsemId())) {
            whereSql.append(" and xsem_id = '" + queParams.getXsemId() + "'");
        }
        //生命线类型
        if (!PlatformObjectUtils.isEmpty(queParams.getLineType())) {
            whereSql.append(" and line_type = '" + queParams.getLineType() + "'");
        }
        //生命线名称
        if (!PlatformObjectUtils.isEmpty(queParams.getLifelineName())) {
            whereSql.append(" and lifeline_name = '" + queParams.getLifelineName() + "'");
        }
        //调查员
        if (!PlatformObjectUtils.isEmpty(queParams.getInvestigator())) {
            whereSql.append(" and investigator  like '%" + queParams.getInvestigator() + "%'");
        }
        //生命线工程编号
        if (!PlatformObjectUtils.isEmpty(queParams.getLifelineCode())) {
            whereSql.append(" and lifeline_code like '%" + queParams.getLifelineCode() + "%'");
        }
        //推测烈度
        if (!PlatformObjectUtils.isEmpty(queParams.getIntensity())) {
            whereSql.append(" and intensity = '" + queParams.getIntensity() + "'");
        }
        //审核状态
        if (!PlatformObjectUtils.isEmpty(queParams.getStatus())) {
            whereSql.append(" and status = '" + queParams.getStatus() + "'");
        }
        sql.append(whereSql).append(orderSql);
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(Lifeline.class));
    }

    @Override
    public List<LifelineType> queryLifelineTypeAll(String level) {
        StringBuilder sql = new StringBuilder("select * from xczh_lifeline_type ");
        StringBuilder orderSql = new StringBuilder(" order by id ASC");
        if (!PlatformObjectUtils.isEmpty(level)) {
            sql.append(" where level = '" + level + "'");
        }
        sql.append(orderSql);
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(LifelineType.class));
    }

    @Override
    public Lifeline findById(String id) {
        String sql = "select * from " + TABLE_NAME + " where " + TABLE_ID + " = '" + id + "'";
        try {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Lifeline.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public void audit(String id,String suggestion,String status) {
        String sql = " update "+TABLE_NAME+" set status = '"+status+"' , suggestion = '"+suggestion+"'  where "+TABLE_ID+" = '"+id+"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public void updateById(Lifeline param) {
        String sql = "update " + TABLE_NAME +
                " set sample_name = :sampleName,lifeline_name = :lifelineName," +
                "line_type = :lineType,line_type2 = :lineType2,damage=:damage,intensity=:intensity,detail_desc=:detailDesc," +
                "update_user=:updateUser,update_time=:updateTime " +
                "where del_flag = '0' and " + TABLE_ID + " = '"+param.getXlrId()+"'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(param);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public boolean save(Lifeline lifeline) {
        String sql = "insert into xczh_lifeline_review values(:xlrId,:xsemId,:province,:city,:district,:detailAddress,:sampleName," +
                ":longitude,:latitude,:lineType,:damage,:intensity,:detailDesc,:enclosurePhoto,:investigator,:investigationTime,:status,:suggestion,:createUser,:createTime,:updateUser," +
                ":updateTime,:delFlag,:lifelineCode,:lifelineName,:investigatorId,:lineType2,:sampleId,:xstmId,:submit,:enclosureVideo,:enclosureAudio,ST_GeometryFromText(:geom,4490))";
        SqlParameterSource source = new BeanPropertySqlParameterSource(lifeline);
        return namedParameterJdbcTemplate.update(sql, source) > 0;
    }

    @Override
    public void submit(List<String> id) {
        String sql="update "+TABLE_NAME+" set submit= '1' where "+TABLE_ID+" in  ('" + String.join("','", id) + "')";
        jdbcTemplate.update(sql);
    }

    @Override
    public void updateXsemId(String xsemId, String convertedXsemId) {
        String sql = "update "+TABLE_NAME+" set xsem_id = '"+xsemId+"'  where xsem_id = '"+convertedXsemId+"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public void batchDelete(List<String> ids) {
        String sql="update "+TABLE_NAME+" set del_flag= '1' where "+TABLE_ID+"  in  ('" + String.join("','", ids) + "')";
        jdbcTemplate.update(sql);
    }

    @Override
    public String getMaxNum(String str) {
        String sql="select max(lifeline_code) from "+TABLE_NAME+" where del_flag = '0' and lifeline_code like '%"+str+"%'";
        String maxNum=jdbcTemplate.queryForObject(sql,String.class);
        return maxNum;
    }

    @Override
    public Integer countByXsemId(String xsemId) {
        String sql ="select count(*) from "+TABLE_NAME+" where del_flag='0' and xsem_id='"+xsemId+"'";
        return jdbcTemplate.queryForObject(sql, Integer.class);
    }
}
